use crate::db::db_get;
use crate::search_engine::get_search_engine_paper;
use crate::{
    app_error::AppError,
    app_writer::AppResult,
    dtos::paper::{PaperAddRequest, PaperResponse, PaperUpdateRequest},
    entities::papers::Paper,
    search_engine::SearchEngine,
};


pub async fn add_paper(req: PaperAddRequest, user_id: &str) -> AppResult<PaperResponse> {
    let db = db_get()?;
    let mut tx = db.begin().await?;
    match {
        let id = uuid::Uuid::new_v4().to_string();
        let _ = sqlx::query!(
            r#"
                INSERT INTO papers (id ,title, author, abstract) 
                VALUES ($1, $2, $3, $4)
                "#,
            id,
            req.title,
            req.author,
            req.abstract_
        )
        .execute(tx.as_mut())
        .await?;
        let _ = sqlx::query!(
            r#"
            INSERT INTO users_papers(user_id, paper_id)
            VALUES ($1, $2)"#,
            user_id,
            id
        )
        .execute(tx.as_mut())
        .await?;
        Ok(id)
    } {
        Ok(id) => {
            tx.commit().await?;
            Ok(PaperResponse {
                id,
                title: req.title,
                author: req.author,
                abstract_: req.abstract_,
            })
        }
        Err(e) => {
            tx.rollback().await?;
            Err(e)
        }
    }
}

/**
 * 使用户拥有该paper
 */
pub async fn own_paper(user_id: &str, paper_id: &str) -> AppResult<()> {
    let db = db_get()?;
    let _ = sqlx::query!(
        r#"
        INSERT INTO users_papers(user_id, paper_id)
        VALUES ($1, $2)
    "#,
        user_id,
        paper_id
    )
    .execute(db)
    .await?;
    Ok(())
}

/**
 * 检查用户是否拥有该paper
 */
pub async fn is_own_paper(user_id: &str, paper_id: &str) -> AppResult<bool> {
    let db = db_get()?;
    let res = sqlx::query!(
        r#"
        SELECT user_id FROM users_papers
        WHERE user_id = $1 AND paper_id = $2
    "#,
        user_id,
        paper_id
    )
    .fetch_optional(db)
    .await?
    .is_some();
    Ok(res)
}

pub async fn papers(user_id: &str) -> AppResult<Vec<PaperResponse>> {
    let db = db_get()?;
    let papers = sqlx::query_as!(
        Paper,
        r#"
        SELECT id, title, author, abstract AS abstract_ FROM papers 
        INNER JOIN users_papers ON papers.id = users_papers.paper_id
        WHERE users_papers.user_id = $1;
        "#,
        user_id
    )
    .fetch_all(db)
    .await?;
    let res = papers
        .into_iter()
        .map(|p| PaperResponse::from(p))
        .collect::<Vec<_>>();
    Ok(res)
}

pub async fn paper(paper_id: &str) -> AppResult<PaperResponse> {
    let db = db_get()?;
    let paper = sqlx::query_as!(
        Paper,
        r#"
        SELECT id, title, author, abstract AS abstract_ FROM papers
        WHERE id = $1
        "#,
        paper_id
    )
    .fetch_one(db)
    .await?;
    Ok(PaperResponse::from(paper))
}

pub async fn update_paper(req: PaperUpdateRequest, user_id: &str) -> AppResult<PaperResponse> {
    let db = db_get()?;
    let mut tx = db.begin().await?;
    match {
        let _ = sqlx::query!(
            r#"
            SELECT user_id FROM users_papers
            WHERE user_id = $1 AND paper_id = $2
        "#,
            user_id,
            req.id
        )
        .fetch_optional(&mut *tx)
        .await?
        .ok_or(AppError::AuthError("not own paper"))?;
        let _ = sqlx::query!(
            r#"
                UPDATE papers
                SET title = $1, author = $2, abstract = $3
                WHERE id = $4
            "#,
            req.title,
            req.author,
            req.abstract_,
            req.id
        )
        .execute(&mut *tx)
        .await?;
        Ok(())
    } {
        Ok(_) => {
            tx.commit().await?;
            Ok(PaperResponse {
                id: req.id,
                title: req.title,
                author: req.author,
                abstract_: req.abstract_,
            })
        }
        Err(e) => {
            tx.rollback().await?;
            Err(e)
        }
    }
}

pub async fn dis_own_paper(user_id: &str, paper_id: &str) -> AppResult<()> {
    let db = db_get()?;
    sqlx::query!(
        r#"
            DELETE FROM users_papers
            WHERE user_id = $1 AND paper_id = $2
            "#,
        user_id,
        paper_id,
    )
    .execute(db)
    .await?;
    Ok(())
}

/**
 * 清理没有用户的paper
 */
pub async fn clean_up() -> AppResult<()> {
    let db = db_get()?;
    let rec = sqlx::query!(
        r#"
            DELETE FROM papers
            WHERE id NOT IN (SELECT paper_id FROM users_papers)
            RETURNING id;
        "#,
    )
    .fetch_all(db)
    .await?;
    // 清理索引
    let search_engine = get_search_engine_paper().await?;
    for row in &rec {
        let paper_id = row.id.as_str();
        let _ = search_engine.delete_paper(paper_id).await;
    }
    if !rec.is_empty() {
        search_engine.commit().await?;
    }
    Ok(())
}
